package com.duowan.test.yzq.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.duowan.cms.common.util.StringUtil;
import com.duowan.cms.domain.channel.Channel;

public class CreateSQLScript {

    private String basePath;
    private String line;

    @Before
    public void setUp() throws Exception {
        basePath = "G:/yangzhuangqiu/workProject/new_cms_fabu/cmsfabu/docs/数据库整理/上线需要执行的sql脚本/";
        line = System.getProperty("line.separator");
    }

    @After
    public void tearDown() throws Exception {
    }

    @Test
    public void testSave() {

    }

    /**
     * CREATE TABLE `channel` (
    `id` char(18) NOT NULL,
    `name` char(32) NOT NULL,
    `domain` varchar(64) DEFAULT NULL,
    `article_file_path` varchar(128) DEFAULT NULL,
    `pic_domain` varchar(64) DEFAULT NULL,
    `pic_file_path` varchar(128) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE INDEX name ON channel (name);
     */
    @Test
    public void testChannelScript() {
        String path = basePath + "channel_table.sql";
        String content = CreatChannelTableScript();
        writeFile(path, content);
    }

    private String CreatChannelTableScript() {
        
        String createSQL = "CREATE TABLE `channel` (`id` char(18) NOT NULL,`name` char(32) NOT NULL,`domain` varchar(64) DEFAULT NULL,`article_file_path` varchar(128) DEFAULT NULL,`pic_domain` varchar(64) DEFAULT NULL,`pic_file_path` varchar(128) DEFAULT NULL,`isimportant` BOOLEAN default FALSE,`upline` BOOLEAN default true,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        
        //String createSQL = "CREATE TABLE `channel` (`id` char(18) NOT NULL,`name` char(32) NOT NULL,`domain` varchar(64) DEFAULT NULL,`article_file_path` varchar(128) DEFAULT NULL,`pic_domain` varchar(64) DEFAULT NULL,`pic_file_path` varchar(128) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        String alterSQL = "CREATE INDEX name ON channel (name);";

        StringBuffer sb = new StringBuffer();
        sb.append(createSQL).append(line).append(alterSQL).append(line);

        for (Channel c : getChannelList()) {
            
            String picDomain = c.getPicDomain();
            if(!StringUtil.isEmpty(picDomain)){
                picDomain = picDomain.replaceFirst("pic.duowan.com", "img.dwstatic.com");
            }
            
            String insertSQL = "INSERT INTO `channel` VALUES ('" + c.getId() + "', '" + c.getName() + "', '" + c.getDomain() + "', '" + c.getArticleFilePath() + "', '"
                    + picDomain + "', '" + c.getPicFilePath() + "', 0, 1);";
            sb.append(insertSQL).append(line);
        }
        return sb.toString();
    }

    @Test
    public void testList_ChannelId() {
        String path = basePath + "list_channelId_table.sql";
        String content = CreateList_ChannelIdTableScript();
        StringBuffer sb = new StringBuffer();
        sb.append("START TRANSACTION;").append(line).append(line).append(content).append("COMMIT;");
        writeFile(path, sb.toString());
    }

    private String CreateList_ChannelIdTableScript() {

        StringBuffer sb = new StringBuffer();
        StringBuffer alter_sb = new StringBuffer();
        StringBuffer index_sb = new StringBuffer();
        String alterSQLSuf = " add (isdelete tinyint(4) default 0, diy4 varchar(2000) default null, diy5 varchar(2000) default null, title_color varchar(10) DEFAULT NULL);";

        for (Channel c : getChannelList()) {
            String alterSQL = "alter table list_" + c.getId() + alterSQLSuf;//" add (isdelete tinyint(4) default 0, title_color varchar(255) DEFAULT NULL);";
            alter_sb.append(alterSQL).append(line);
            String indexSQL = "CREATE INDEX articleid ON list_" + c.getId() + " (articleid);";
            index_sb.append(indexSQL).append(line);
        }

        return sb.append(alter_sb).append(index_sb).toString();
    }
    
    

    @Test
    public void testArticle_ChannelId() {
        String path = basePath + "article_channelId_table.sql";
        String content = CreateArticle_ChannelIdTableScript();
        StringBuffer sb = new StringBuffer();
        sb.append("START TRANSACTION;").append(line).append(line).append(content).append("COMMIT;");
        //content = "START TRANSACTION;" + line + content + "COMMIT;";
        writeFile(path, sb.toString());
    }
    
    private String CreateArticle_ChannelIdTableScript() {

        StringBuffer alter_sb = new StringBuffer();
        
        String alterSQLSuf = "  add  (showAllPage BOOLEAN default FALSE,syncWap BOOLEAN default FALSE,diy4 varchar(2000) default null,diy5 varchar(2000) default null);";

        for (Channel c : getChannelList()) {
            String alterSQL = "alter table article_" + c.getId() + alterSQLSuf;
                    //+ "  add  (diy4 varchar(2000) DEFAULT NULL,diy5 varchar(2000) DEFAULT NULL,empty_link varchar(255) DEFAULT NULL,tag_picture_url varchar(255) DEFAULT NULL,showAllPage BOOLEAN default FALSE,syncQa BOOLEAN default FALSE,syncWap BOOLEAN default FALSE,syncZone BOOLEAN default FALSE);";
            alter_sb.append(alterSQL).append(line);
        }
        return alter_sb.toString();
    }
    
    //article_channelId表增加字段:title_color varchar(18) default null
    @Test
    public void testArticle_ChannelId20130320() {
        String path = basePath + "article_channelId_table20130320.sql";
        String content = CreateArticle_ChannelIdTableScript20130320();
        StringBuffer sb = new StringBuffer();
        sb.append("START TRANSACTION;").append(line).append(line).append(content).append("COMMIT;");
        //content = "START TRANSACTION;" + line + content + "COMMIT;";
        writeFile(path, sb.toString());
    }
    private String CreateArticle_ChannelIdTableScript20130320() {

        StringBuffer alter_sb = new StringBuffer();
        
        String alterSQLSuf = "  add  (title_color varchar(18) default null);";

        for (Channel c : getChannelList()) {
            String alterSQL = "alter table article_" + c.getId() + alterSQLSuf;
                    //+ "  add  (diy4 varchar(2000) DEFAULT NULL,diy5 varchar(2000) DEFAULT NULL,empty_link varchar(255) DEFAULT NULL,tag_picture_url varchar(255) DEFAULT NULL,showAllPage BOOLEAN default FALSE,syncQa BOOLEAN default FALSE,syncWap BOOLEAN default FALSE,syncZone BOOLEAN default FALSE);";
            alter_sb.append(alterSQL).append(line);
        }
        return alter_sb.toString();
    }

    /**
     * article_channelId表将title从唯一索引改成普通索引:ALTER TABLE article_channelId DROP INDEX title
     * "CREATE INDEX title ON article_channelId (title);"
     */
    @Test
    public void testArticle_ChannelId20130321() {
        String path = basePath + "article_channelId_table20130321.sql";
        String content = CreateArticle_ChannelIdTableScript20130321();
        StringBuffer sb = new StringBuffer();
        sb.append("START TRANSACTION;").append(line).append(line).append(content).append("COMMIT;");
        //content = "START TRANSACTION;" + line + content + "COMMIT;";
        writeFile(path, sb.toString());
    }
    private String CreateArticle_ChannelIdTableScript20130321() {

        StringBuffer alter_sb = new StringBuffer();
        
        String dropSQLSuf = "  DROP INDEX title;";

        for (Channel c : getChannelList()) {
            String dropSQL = "alter table article_" + c.getId() + dropSQLSuf;
            String createSQL = "CREATE INDEX title ON article_" + c.getId() + " (title);";
                    //+ "  add  (diy4 varchar(2000) DEFAULT NULL,diy5 varchar(2000) DEFAULT NULL,empty_link varchar(255) DEFAULT NULL,tag_picture_url varchar(255) DEFAULT NULL,showAllPage BOOLEAN default FALSE,syncQa BOOLEAN default FALSE,syncWap BOOLEAN default FALSE,syncZone BOOLEAN default FALSE);";
            alter_sb.append(dropSQL).append(createSQL).append(line);
        }
        return alter_sb.toString();
    }
    
    /**
     * Alter table article_ceshi modify `title` varchar(100)  NOT NULL
     */
    @Test
    public void testArticle_ChannelId20130402() {
        String path = basePath + "article_channelId_table20130402.sql";
        String content = CreateArticle_ChannelIdTableScript20130402();
        StringBuffer sb = new StringBuffer();
        sb.append("START TRANSACTION;").append(line).append(line).append(content).append("COMMIT;");
        //content = "START TRANSACTION;" + line + content + "COMMIT;";
        writeFile(path, sb.toString());
    }
    private String CreateArticle_ChannelIdTableScript20130402() {

        StringBuffer alter_sb = new StringBuffer();
        
        String alterSQLSuf = "  modify `title` varchar(100)  NOT NULL;";

        for (Channel c : getChannelList()) {
            String dropSQL = "alter table article_" + c.getId() + alterSQLSuf;
            alter_sb.append(dropSQL).append(line);
        }
        return alter_sb.toString();
    }

    private List<Channel> getChannelList() {

        String[][] cs = ChannelInfoConfigUtil.readChannelinfo();
        List<Channel> list = new ArrayList<Channel>();
        for (int i = 0; i < cs.length; i++) {
            if (cs[i][0] != null && cs[i][0].trim() != "") {
                Channel channel = new Channel();
                channel.setId(cs[i][0]);
                channel.setName(cs[i][1]);
                channel.setDomain(cs[i][2]);
                if (cs[i][3].indexOf("fabu") != -1)
                    cs[i][3] = cs[i][3].replaceFirst("fabu", "cms");
                channel.setArticleFilePath(cs[i][3]);
                channel.setPicDomain(cs[i][4]);
                channel.setPicFilePath(cs[i][5]);
                list.add(channel);
            }
        }
        return list;
    }

    private void writeFile(String path, String content) {
        try {
            OutputStream os = FileUtils.openOutputStream(new File(path));
            IOUtils.write(content, os, "UTF-8");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
